package com.server.controller;

import com.alibaba.excel.EasyExcel;
import com.comm.excel.ExcelUtil;
import com.comm.exception.BusinessException;
import com.comm.result.ResponseResult;
import com.server.entity.dto.ExcelDto;
import com.server.listener.EasyExcelListener;
import com.server.service.DictConfigService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Description: TODO
 * @Author: mis_wu
 * @Date: 2022/6/29
 **/
@RestController
@RequestMapping("/excel")
@Slf4j
@RefreshScope
@Api(tags = "easy-excel测试操作")
public class ExcelController {

    @Value("${save-excel-address}")
    private String excelPath;

    @Resource
    private DictConfigService dictConfigService;

    /**
     * excel导出功能
     * @param excelName name
     * 不用写返回值，可能导致excel表格中是返回值内容，不是导出数据
     */
    @GetMapping("/excelExport")
    @ApiOperation("excel数据导出")
    public void excelExport(@NotNull String excelName){
        if (StringUtils.isBlank(excelName)){
            throw BusinessException.build("文件名称不能为空");
        }
        //实现excel写的操作
        //1.设置写入文件夹的地址，以及excel文件名称
        String filepath = excelPath + "\\" + excelName;

        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        try {
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            //response.setContentType("application/vnd.ms-excel");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            //2.调用EasyExcel里面的方法实现写操作
            //write方法两个参数：第一个参数文件路径名称，第二个参数实体类class
            EasyExcel.write(response.getOutputStream(), ExcelDto.class).sheet("Sheet1").doWrite(getExcelData());
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    public List<ExcelDto> getExcelData(){
        ArrayList<ExcelDto> list = new ArrayList<>();
        for (int i = 0; i < 100000; i++) {
            ExcelDto excelDto = new ExcelDto();
            excelDto.setId(i + 1L);
            excelDto.setName("李四"+i+"号");
            excelDto.setAge(20 + i);
            excelDto.setDate(new Date());
            list.add(excelDto);
        }
        return list;
    }

    @GetMapping("/excelImport")
    @ApiOperation("excel数据导入")
    public ResponseResult<String> excelImport(String filePath){
        log.info("excel path:{}",filePath);
        //调用EasyExcel里面的方法实现读操作
        EasyExcel.read(filePath, ExcelDto.class,new EasyExcelListener()).sheet().doRead();
        return ResponseResult.isSuccess();
    }

    /**
     * 导出excel
     * @param response response
     * @param title title
     * todo:不能用，可能是jar包冲突报错
     */
    @GetMapping("/excelExportXSSF")
    @ApiOperation("excel数据导出")
    public void excelExportByXSSF(HttpServletResponse response, String title){
        try {
            List<Object[]> excelList = new ArrayList<>();
            //列头数据
            String[] colHead = {"id","name","age"};
            Object[] objs = null;
            List<ExcelDto> dataList = getExcelData();
            for (ExcelDto excelDto : dataList) {
                objs = new Object[colHead.length];
                objs[0] = excelDto.getId();
                objs[1] = excelDto.getAge();
                objs[2] = excelDto.getName();
                excelList.add(objs);
            }
            XSSFWorkbook result = ExcelUtil.createExcel(title, colHead, excelList);
            String fileName = title + System.currentTimeMillis() + ".xlsx";
            ExcelUtil.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            result.write(os);
            os.close();
        } catch (IOException e) {
            log.error(e.getMessage());
        }
    }


    /**
     * TODO:可用
     * @param response response
     * @throws Exception io
     */
    @GetMapping("/downloadExcel")
    @ApiOperation("使用HSSF导出excel-缺点:一次性最多导出65536，但07版本可以，百度")
    public void downloadExcel(HttpServletResponse response) throws Exception{
        //创建Excel文件
        Workbook wb = new HSSFWorkbook();
        //生成sheet
        Sheet sheet = wb.createSheet("POI测试sheet");
        //创建行
        Row row = null;
        //创建列
        Cell cell = null;
        //创建表头单元格样式
        CellStyle cs_header = wb.createCellStyle();
        //设置字体样式
        Font boldFont = wb.createFont();
        //设置文字类型
        boldFont.setFontName("宋体");
        //设置加粗
        boldFont.setBold(true);
        //设置文字大小
        boldFont.setFontHeightInPoints((short) 16);
        //应用设置的字体
        cs_header.setFont(boldFont);
        //设置边框下、左、右、上
        cs_header.setBorderBottom(BorderStyle.THIN);
        cs_header.setBorderLeft(BorderStyle.THIN);
        cs_header.setBorderRight(BorderStyle.THIN);
        cs_header.setBorderTop(BorderStyle.THIN);
        //水平居中
        cs_header.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cs_header.setVerticalAlignment(VerticalAlignment.CENTER);
        //前景填充色
        cs_header.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        //设置前景填充样式
        cs_header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置标题
        row = sheet.createRow(0);
        //设置单元格行高
        row.setHeightInPoints(24);
        //设置标题
        String[] headers=new String[]{"type字段ID标题","types字段ID标题","depName字段标题","offName标题"};
        //逐个设置标题样式
        for (int i = 0; i < headers.length; i++) {
            //创建单元格
            cell = row.createCell(i);
            //设置单元格内容
            cell.setCellValue(headers[i]);
            //设置单元格样式
            cell.setCellStyle(cs_header);
        }
        //创建文本单元格样式
        CellStyle cs_text = wb.createCellStyle();
        //创建文字设置
        Font textFont = wb.createFont();
        //设置文字类型
        textFont.setFontName("Consolas");
        //设置文字大小
        textFont.setFontHeightInPoints((short) 10);
        //应用设置
        cs_text.setFont(textFont);
        //设置边框
        cs_text.setBorderBottom(BorderStyle.THIN);
        cs_text.setBorderLeft(BorderStyle.THIN);
        cs_text.setBorderRight(BorderStyle.THIN);
        cs_text.setBorderTop(BorderStyle.THIN);
        //水平居中
        cs_text.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cs_text.setVerticalAlignment(VerticalAlignment.CENTER);
        //调取数据
        List<ExcelDto> excelData = getExcelData();
        if (excelData.size() > 65536){
            excelData = excelData.subList(0,65535);
        }
        //记录总共多少列（由于接口查询出来的实体类集合，所以不好循环，使用）
        int cellSum = 0;
        //将数据写入表格
        for(int i=0; i<excelData.size(); i++){
            //将实体类集合转成string数组，通过‘，’，获取总共多少列
            String[] split = excelData.get(i).toString().split(",");
            cellSum=split.length;
            //创建行，由于0行是标题，所以+1
            row = sheet.createRow(i+1);
            //实体类集合不太好循环，所以逐一设置，如果是其他则可使用for循环
            ExcelDto wpa = excelData.get(i);
            row.createCell(0).setCellValue(wpa.getId()!=null?wpa.getId():'1');
            row.createCell(1).setCellValue(wpa.getAge()!=null?wpa.getAge():'2');
            row.createCell(2).setCellValue(wpa.getName()!=null?wpa.getName():"3");
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            row.createCell(3).setCellValue(wpa.getDate()!=null?df.format(wpa.getDate()):df.format(new Date()));
            //为每一个单元格设置样式
            for (int j=0;j<cellSum;j++){
                row.getCell(j).setCellStyle(cs_text);
            }
        }
        //合并单元格，横向
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 11, 12));
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1,1,3,4));
        //竖着合并
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1,excelData.size(),4,4));
        //设置单元格宽度自适应
        for (int i = 0; i <= cellSum; i++)
        {
            sheet.autoSizeColumn((short)i,true); //自动调整列宽
        }
        //设置中文文件名称
        String fileName = URLEncoder.encode("POIExcel下载测试","UTF-8");
        //浏览器默认服务器传过去的是html，不是excel文件
        //设置响应类型:传输内容是流，并支持中文
        response.setContentType("application/octet-stream;charset=UTF-8");
        //设置响应头信息header，下载时以文件附件下载
        response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls");
        //输出流对象
        OutputStream os = response.getOutputStream();
        wb.write(os);
        //强制刷新
        os.flush();
        os.close();
        wb.close();
    }


}
